
package com.compuvest.webstat;

// <editor-fold defaultstate="collapsed" desc="import">
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import javax.naming.Context;
import javax.naming.InitialContext;

import java.util.logging.Level;
import java.util.logging.Logger;
// </editor-fold>

public final class WMySQL implements java.io.Serializable {

    private static final Logger logger = Logger.getLogger(WMySQL.class.getName());

    private DataSource dsw;

// <editor-fold defaultstate="collapsed" desc="WMySQL()">

    public WMySQL() {

	try {
            Context initCtx = new InitialContext();
            dsw = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) {
            logger.log(Level.SEVERE, "init()\n", e);
        }
        logger.log(Level.INFO, getVersion());

    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVersion()">

    public String getVersion() {

	String version = "Error";
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement("SELECT version() version");
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                version = rs.getString("version");
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getVersion()", e);
        }
	return version;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCustomers()">

    public String getCustomers(
            String startDate,   //
            String endDate,     //
            int currentPage,   // current page
            int custOnPage,     // how many show customers on page (15,25,50)
            int custid
    ) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;
            json.append("{rows:[");

            PreparedStatement ps = null;

            if(custid == 0) {
                ps = conn.prepareStatement(
                  "SELECT CONCAT(" +
                    "'id:',c.customer_id," +
                    "',resl:',IF(c.type='R',1,0)," + // if reseller = true
                    "',accw:',QUOTE(c.accwareAccount)," +
                    "',no:',QUOTE(c.last_order_no)," +
                    "',email:',QUOTE(c.email)," +
                    "',name:',QUOTE(CONCAT(c.billingFirstName,' ',c.billingLastName))," +
                    "',bphone:',QUOTE(c.billingPhone)," +
                    "',orders:',c.count_order," +
                    "',amount:',c.sum_order" +
                    ") json,c.joined date " +
                  "FROM sklad.customer c " +
                  "LEFT JOIN sklad.orders o USING(customer_id) " +
                  "WHERE c.joined BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59' AND o.order_id IS NULL " +
                "UNION " +
                  "SELECT CONCAT( " +
                    "'id:',c.customer_id," +
                    "',resl:',IF(c.type='R',1,0)," + // if reseller = true
                    "',email:',QUOTE(c.email)," +
                    "',accw:',QUOTE(c.accwareAccount)," +
                    "',no:',QUOTE(c.last_order_no)," +
                    "',name:',QUOTE(CONCAT(c.billingFirstName,' ',c.billingLastName))," +
                    "',bphone:',QUOTE(c.billingPhone)," +
                    "',orders:',c.count_order," +
                    "',amount:',c.sum_order" +
                    ") json,o.order_date date " +
                  "FROM sklad.orders o " +
                  "LEFT JOIN sklad.customer c USING(customer_id) " +
                  "WHERE o.order_date BETWEEN '" + startDate + " 00:00:00' and '" + endDate + " 23:59:59' " +
                  "ORDER BY date " +
                  "LIMIT ?,?"
                );
                ps.setInt(1, currentPage * custOnPage);
                ps.setInt(2, custOnPage);
            }
            else {
                ps = conn.prepareStatement(
                  "SELECT CONCAT(" +
                    "'id:',c.customer_id," +
                    "',resl:',IF(c.type='R',1,0)," + // if reseller = true
                    "',accw:',QUOTE(c.accwareAccount)," +
                    "',no:',QUOTE(c.last_order_no)," +
                    "',email:',QUOTE(c.email)," +
                    "',name:',QUOTE(CONCAT(c.billingFirstName,' ',c.billingLastName))," +
                    "',bphone:',QUOTE(c.billingPhone)," +
                    "',orders:',c.count_order," +
                    "',amount:',c.sum_order" +
                    ") json,c.joined date " +
                  "FROM sklad.customer c " +
                  "WHERE c.customer_id=?"
                );
                ps.setInt(1, custid);
            }

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append("{").append(rs.getString("json")).append(",date:'").append(rs.getString("date")).append("'}");
            }
            rs.close();
            ps.close();
            json.append("]");

            // customer count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getCustomers()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrders()">

    public String getOrders(
            String startDate,
            String endDate,
            int currentPage,   // current page
            int custOnPage,    // how many show customers on page (15,25,50)
            int orderid
    ) {

        String where = "order_date BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59' ";
        if(orderid != 0) {
            where = "order_id=" + orderid + " ";
        }

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;
            json.append("{rows:[");

            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                "'{id:',order_id," +
                "',date:',QUOTE(order_date)," +
                "',no:',QUOTE(orderNo)," +
                "',cod:',QUOTE(cod)," +
                "',wty:',QUOTE(warranty)," +
                "',sh_pr:',shipping_price," +
                "',sh_md:',QUOTE(shipping_code)," +
                "',tax:',tax_price," +
                "',amount:',amount," +
                "',impt:',QUOTE(import)," +
                "'}') json " +
                "FROM sklad.orders " +
                "WHERE " + where +
                "ORDER BY order_date LIMIT ?,?"
            );
            ps.setInt(1, currentPage * custOnPage);
            ps.setInt(2, custOnPage);

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // orders count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getOrders()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrder()">

    static String GET_ORDER = "SELECT order_cp FROM sklad.orderHistory WHERE order_id=?";

    public String getOrder(String orderID) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection();
            PreparedStatement ps = conn.prepareStatement(GET_ORDER)) {
            ps.setString(1, orderID);

            try(ResultSet rs = ps.executeQuery()) {
                while(rs.next()) {
                    json.append(rs.getString("order_cp"));
                }
            }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getOrder()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrdersForAdvert()">

    public String getOrdersForAdvert(
            String advert,
            String startDate,   //
            String endDate,     //
            int currentPage,   // current page
            int custOnPage     // how many show customers on page (15,25,50)
    ) {

        StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            json.append("{rows:[");

            int idx = 0;
            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                "'{id:',o.order_id," +
                "',cid:',c.customer_id," +
                "',date:',QUOTE(o.order_date)," +
                "',name:',QUOTE(CONCAT(c.billingFirstName,' ',c.billingLastName))," +
                "',email:',QUOTE(c.email)," +
                "',no:',QUOTE(o.orderNo)," +
                "',orders:',c.count_order," +
                "',amount:',o.amount," +
                "'}') json " +
                "FROM sklad.orders o " +
                "LEFT JOIN sklad.customer c USING(customer_id) " +
                "LEFT JOIN sklad.advert ad USING(advert_id) " +
                "WHERE o.order_date BETWEEN CONCAT(CURRENT_DATE(),' ','00:00:00') AND CONCAT(CURRENT_DATE(),' ','23:59:59') " +
                "AND c.customer_id IS NOT NULL AND ad.name=? " +
                "ORDER BY o.order_date LIMIT ?,?"
            );
            ps.setString(1, advert);
            ps.setInt(2, currentPage * custOnPage);
            ps.setInt(3, custOnPage);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // order count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");
        }
        catch(SQLException e) { 
            logger.log(Level.SEVERE, "getOrdersForAdvert()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCustomersOrders()">
    static String GET_CUSTOMERS_ORDERS =
        "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
        "'{id:',order_id," +
        "',date:',QUOTE(order_date)," +
        "',no:',QUOTE(orderNo)," +
        "',cod:',QUOTE(cod)," +
        "',wty:',QUOTE(warranty)," +
        "',sh_pr:',shipping_price," +
        "',sh_md:',QUOTE(shipping_code)," +
        "',tax:',tax_price," +
        "',amount:',amount," +
        "'}') json " +
	"FROM sklad.orders " +
        "WHERE customer_id=? " +
        "ORDER BY order_date DESC  LIMIT ?,?";

    public String getCustomersOrders(
            String customer_id,
            int currentPage,   // current page
            int custOnPage     // how many show customers on page (15,25,50)
    ) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            json.append("{rows:[");
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(GET_CUSTOMERS_ORDERS);
            ps.setString(1, customer_id);
            ps.setInt(2, currentPage * custOnPage);
            ps.setInt(3, custOnPage);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // customer orders count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getCustomersOrders()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="RESELLERS">

// <editor-fold defaultstate="collapsed" desc="getResellerInfo()">

    public String getResellerInfo(int custid) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                  "'{id:',c.customer_id," +
                  "',email:',QUOTE(c.email)," +
                  "',code:',QUOTE(c.reseller_code)," +
                  "',type:',QUOTE(c.type)," +
                  "',accw:',QUOTE(c.accwareAccount)," +
                  "',date:',QUOTE(c.joined)," +
                  "',orders:',c.count_order," +
                  "',sum:',c.sum_order," +
                  "',note:',QUOTE(REPLACE(REPLACE(c.notice,'\r',''),'\n',''))," +
                  "',billing:',CONCAT('{'," +
                    "'company:',QUOTE(c.billingCompany)," +
                    "',name:',QUOTE(CONCAT(c.billingFirstName,' ',c.billingLastName))," +
                    "',address1:',QUOTE(c.billing_address1)," +
                    "',address2:',QUOTE(c.billing_address2)," +
                    "',address:',QUOTE(CONCAT(c.billing_city,' ',c.billing_state,',',billing_zip,' ',billing_country))," +
                    "',phone:',QUOTE(c.billingPhone)," +
                    "',cell:',QUOTE(c.billingCell)," +
                    "',fax:',QUOTE(c.billingFax)," +
                  "'}')," +
                  "',shipping:',CONCAT('{'," +
                    "'company:',QUOTE(c.shippingCompany)," +
                    "',name:',QUOTE(CONCAT(c.shippingFirstName,' ',c.shippingLastName))," +
                    "',address1:',QUOTE(c.shipping_address1)," +
                    "',address2:',QUOTE(c.shipping_address2)," +
                    "',address:',QUOTE(CONCAT(c.shipping_city,' ',c.shipping_state,',',shipping_zip,' ',shipping_country))," +
                    "',phone:',QUOTE(c.shippingPhone)," +
                    "',cell:',QUOTE(c.shippingCell)," +
                    "',fax:',QUOTE(c.shippingFax)," +
                  "'}')," +
                  "'}') json " +
                "FROM sklad.customer c " +
                "WHERE c.customer_id=?"
            );
            ps.setInt(1, custid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getResellerInfo()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveResellerInfo()">

    public String saveResellerInfo(
        int custid,
        String accw, // accware Account
        String type, // C - customer, R - reseller
        String note  //
    ) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement(
                "UPDATE LOW_PRIORITY sklad.customer SET accwareAccount=?,type=?,notice=? WHERE customer_id=?");
	    us.setString(1, accw);
	    us.setString(2, type);
	    us.setString(3, note);
	    us.setInt(4, custid);

	    us.executeUpdate();
	    us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveResellerInfo()", e);
        }
	return "aprove";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getResellers()">

    public String getResellers(
            String startDate, //
            String endDate,   //
            int currentPage,  // current page
            int custOnPage,   // how many show customers on page (15,25,50)
            int custid        // col id
    ) {

        String where = "c.joined BETWEEN '" + startDate + " 00:00:00' AND '" + endDate + " 23:59:59' "; //AND c.type='R'
        if(custid != 0) {
            where = "c.customer_id=" + custid + " ";
        }

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;
            json.append("{rows:[");

            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                  "'{id:',c.customer_id," +
                  "',email:',QUOTE(c.email)," +
                  "',code:',QUOTE(c.reseller_code)," +
                  "',type:',QUOTE(c.type)," +
                  "',accw:',QUOTE(c.accwareAccount)," +
                  "',name:',QUOTE(CONCAT(c.billingFirstName,' ',c.billingLastName))," +
                  "',date:',QUOTE(c.joined)," +
                  "',bphone:',QUOTE(c.billingPhone)," +
                  "',orders:',c.count_order," +
                  "'}') json " +
                "FROM sklad.customer c " +
                "WHERE " + where +
                "ORDER BY c.joined ASC LIMIT ?,?"
            );
            ps.setInt(1, currentPage * custOnPage);
            ps.setInt(2, custOnPage);

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // customer count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getResellers()", e);
        }
	return json.toString();
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="GOOGLE">

// <editor-fold defaultstate="collapsed" desc="getGoogleTable()">

    public String getGoogleTable() {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;
            json.append("{groups:[");

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{id:',group_id," +
                "',name:',QUOTE(name)," +
                "',fname:',QUOTE(fileName)," +
                "',cpcmax:',cpcmax," +
                "',cpccnt:',cpccont," +
                "',cnt:',itemsCount," +
                "',active:',active," +
                "'}') json " +
                "FROM advert.googleGroups " +
                "ORDER BY group_id"
            );

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append("]}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getGoogleTable()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getGoogleGroup()">

    public String getGoogleGroup(String groupid) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{id:',group_id," +
                "',name:',QUOTE(name)," +
                "',fname:',QUOTE(fileName)," +
                "',cpcmax:',cpcmax," +
                "',cpccnt:',cpccont," +
                "',active:',active," +
                "'}') json " +
                "FROM advert.googleGroups " +
                "WHERE group_id=?"
            );
            ps.setString(1, groupid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getGoogleGroup()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateGroup()">

    public String updateGroup(
        String groupid,
        String name,
        String fname,
        double cpcmax,
        double cpcnt,
        String active
    ) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement(
                "UPDATE LOW_PRIORITY advert.googleGroups SET name=?,fileName=?,cpcmax=?,cpccont=?,active=? WHERE group_id=?");
	    us.setString(1, name);
	    us.setString(2, fname);
	    us.setDouble(3, cpcmax);
	    us.setDouble(4, cpcnt);
	    us.setString(5, active);
	    us.setString(6, groupid);
	    us.executeUpdate();

            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "updateGroup()", e);
        }
	return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="createGroup()">

    public String createGroup(
        String name,
        String fname,
        Double cpcmax,
        Double cpcnt,
        String active
    ) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement(
                "INSERT LOW_PRIORITY advert.googleGroups SET name=?,fileName=?,cpcmax=?,cpccont=?,active=?");
	    us.setString(1, name);
	    us.setString(2, fname);
	    us.setDouble(3, cpcmax);
	    us.setDouble(4, cpcnt);
	    us.setString(5, active);
	    us.executeUpdate();

            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "createGroup()", e);
        }
	return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="eraseGroup()">

    public String eraseGroup(String groupid) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement("DELETE FROM advert.googleGroups WHERE group_id=?");
	    us.setString(1, groupid);
	    us.executeUpdate();

            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "eraseGroup()", e);
        }
	return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCategory()">

    public String getCategory(String groupid) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = null;

            if("".equals(groupid)) { // select all category
                ps = conn.prepareStatement(
                    "SELECT CONCAT('{id:',category_id,',name:',QUOTE(name),'}') json " +
                    "FROM sklad.category " +
                    "WHERE parent=0 AND publish='Y' " +
                    "ORDER BY name");
            }
            else { // select selecteg google group
                ps = conn.prepareStatement(
                    "SELECT CONCAT('{id:',scg.category_id,',name:',QUOTE(scg.name),'}') json " +
                    "FROM advert.googleSend ads " +
                    "LEFT JOIN sklad.category sc ON ads.subcat_id=sc.category_id " +
                    "LEFT JOIN sklad.category scg ON sc.parent=scg.category_id " +
                    "WHERE  ads.group_id=? AND sc.publish='Y' AND scg.publish='Y' " +
                    "GROUP BY scg.category_id " +
                    "ORDER BY scg.name");
                ps.setString(1, groupid);
            }

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getCategory()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getSubCategory()">

    public String getSubCategory(String catid) {

        int idx;
	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {

            json.append("{sub:[");
            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{cid:',sc.category_id," +
                "',cname:',QUOTE(sc.name)," +
                "',vid:',it.vendor_id," +
                "',vname:',QUOTE(iv.name)," +
                "',alias:',QUOTE(iv.alias)," +
                "',cnt:',count(*)," +
                "'}'),'{}') json " +
                "FROM sklad.items it " +
                "LEFT JOIN sklad.category sc USING(category_id) " +
                "LEFT JOIN sklad.itemVendor iv USING(vendor_id) " +
                "WHERE it.active='Y' AND sc.parent=? " +
                "GROUP BY it.vendor_id,sc.category_id");
            ps.setString(1, catid);

            idx = 0;
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append("],grp:[");
            ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{gid:',ads.group_id," +
                "',gname:',QUOTE(ggs.fileName)," +
                "',vid:',ads.vendor_id," +
                "',cid:',ads.subcat_id," +
                "'}'),'{}') json " +
                "FROM advert.googleSend ads " +
                "LEFT JOIN advert.googleGroups ggs USING(group_id) " +
                "WHERE subcat_id IN(" +
                  "SELECT category_id FROM sklad.category WHERE parent=? AND publish='Y'" +
                ")");
            ps.setString(1, catid);

            idx = 0;
            rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append("]}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getSubCategory()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveCategories()">

    public String saveCategories(String groupid, String catid, String cats) {

        String[] checkbox = cats.split(",");

        try(Connection conn = dsw.getConnection()) {
            String[] ids;
            String subcatid, vendid;
            boolean chbox;

	    PreparedStatement deleteBatch = conn.prepareStatement(
                "DELETE LOW_PRIORITY FROM advert.googleSend WHERE group_id=? AND vendor_id=? AND subcat_id IN(" +
                "SELECT category_id FROM sklad.category WHERE parent=?)");
            PreparedStatement insertBatch = conn.prepareStatement(
                "INSERT LOW_PRIORITY advert.googleSend SET group_id=?,subcat_id=?,vendor_id=?");

            //   ids=  0  1 3
            // cats = 651_2_0 -> where 651 - subcatid; 2 - vendorid; 0/1 - checkbox
            for(String cat: checkbox) {
                ids = cat.split("_");
                subcatid = ids[0];
                vendid = ids[1];
                chbox = "1".equals(ids[2]) ? true: false;

                // delete all subcat
                deleteBatch.setString(1, groupid);
                deleteBatch.setString(2, vendid);
                deleteBatch.setString(3, catid);
                deleteBatch.addBatch();

                // insert
                if(chbox) {
                    insertBatch.setString(1, groupid);
                    insertBatch.setString(2, subcatid);
                    insertBatch.setString(3, vendid);
                    insertBatch.addBatch();
                }
            }
            deleteBatch.executeBatch();
            deleteBatch.close();

            insertBatch.executeBatch();
            insertBatch.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveCategories()", e);
        }
	return "";
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="EPY">

// <editor-fold defaultstate="collapsed" desc="getEpyVendors()">

    public String getEpyVendors() {

	StringBuilder json = new StringBuilder();
        int idx = 0;
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                    "'{vid:',sv.vendor_id," +
                    "',als:',QUOTE(sv.alias)," +
                    "',name:',QUOTE(sv.name)," +
                    "',cnt:',COUNT(it.vendor_id)," +

                    "',amazonMargin:',IFNULL(av.amazonMargin,0)," +
                    "',amazonCharge:',IFNULL(av.amazonCharge,0)," +
                    "',amazonQty:',IFNULL(av.amazonQty,0)," +
                    "',amazonSendQty:',IFNULL(av.amazonSendQty,0)," +

                    "',amzcompMargin:',IFNULL(av.amzcompMargin,0)," +
                    "',amzcompCharge:',IFNULL(av.amzcompCharge,0)," +
                    "',amzcompQty:',IFNULL(av.amzcompQty,0)," +
                    "',amzcompSendQty:',IFNULL(av.amzcompSendQty,0)," +

                    "',ebayMargin:',IFNULL(av.ebayMargin,0)," +
                    "',ebayCharge:',IFNULL(av.ebayCharge,0)," +
                    "',ebayQty:',IFNULL(av.ebayQty,0)," +
                    "',ebaySendQty:',IFNULL(av.ebaySendQty,0)," +

                    "',ebayAliMargin:',IFNULL(av.ebayAliMargin,0)," +
                    "',ebayAliCharge:',IFNULL(av.ebayAliCharge,0)," +
                    "',ebayAliQty:',IFNULL(av.ebayAliQty,0)," +
                    "',ebayAliSendQty:',IFNULL(av.ebayAliSendQty,0)," +

                    "',buycomMargin:',IFNULL(av.buycomMargin,0)," +
                    "',buycomCharge:',IFNULL(av.buycomCharge,0)," +
                    "',buycomQty:',IFNULL(av.buycomQty,0)," +
                    "',buycomSendQty:',IFNULL(av.buycomSendQty,0)," +

                    "',active:',IFNULL(av.active,0)," +
                  "'}') json " +
                "FROM sklad.itemVendor sv " +
                "LEFT JOIN advert.epyVendors av USING (vendor_id) " +
                "LEFT JOIN sklad.items it USING (vendor_id) " +
                "GROUP BY it.vendor_id"
            );

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyVendors()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyVendorsNew()">

    public String getEpyVendorsNew(
        int currentPage,   // current page
        int custOnPage     // how many show customers on page (15,25,50)
    ) {

	StringBuilder json = new StringBuilder();
        int idx = 0;
        try(Connection conn = dsw.getConnection()) {

            json.append("{rows:[");
            PreparedStatement ps = conn.prepareStatement(
                "SELECT " +
                  "CONCAT(" +
                    "'[',QUOTE(sv.alias)," +
                    "',',QUOTE(sv.name)," +
                    "',',QUOTE(CONCAT(IFNULL(av.amazonMargin,0.00),'%-$',IFNULL(av.amazonCharge,0.00),'-',IFNULL(av.amazonQty,0)))," +
                    "',',QUOTE(CONCAT(IFNULL(av.ebayMargin,0.00),'%-$',IFNULL(av.ebayCharge,0.00),'-',IFNULL(av.ebayQty,0)))," +
                    "',',QUOTE(CONCAT(IFNULL(av.buycomMargin,0.00),'%-$',IFNULL(av.buycomCharge,0.00),'-',IFNULL(av.buycomQty,0)))," +

                  "']') json " +
                "FROM sklad.itemVendor sv " +
                "LEFT JOIN advert.epyVendors av USING (vendor_id) " +
                "LIMIT ?,?"
            );
            ps.setInt(1, currentPage * custOnPage);
            ps.setInt(2, custOnPage);

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // vendors count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyVendorsNew()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyVendor()">

    public String getEpyVendor(String vendid) {

	String json = "";
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT " +
                  "CONCAT(" +
                    "'{vid:',sv.vendor_id," +
                    "',als:',QUOTE(sv.alias)," +
                    "',name:',QUOTE(sv.name)," +

                    "',amazonMargin:',QUOTE(IFNULL(av.amazonMargin,0.00))," +
                    "',amazonCharge:',QUOTE(IFNULL(av.amazonCharge,0.00))," +
                    "',amazonQty:',IFNULL(av.amazonQty,0)," +

                    "',amzcompMargin:',QUOTE(IFNULL(av.amzcompMargin,0.00))," +
                    "',amzcompCharge:',QUOTE(IFNULL(av.amzcompCharge,0.00))," +
                    "',amzcompQty:',IFNULL(av.amzcompQty,0)," +

                    "',ebayMargin:',QUOTE(IFNULL(av.ebayMargin,0.00))," +
                    "',ebayCharge:',QUOTE(IFNULL(av.ebayCharge,0.00))," +
                    "',ebayQty:',IFNULL(av.ebayQty,0)," +

                    "',ebayAliMargin:',QUOTE(IFNULL(av.ebayAliMargin,0.00))," +
                    "',ebayAliCharge:',QUOTE(IFNULL(av.ebayAliCharge,0.00))," +
                    "',ebayAliQty:',IFNULL(av.ebayAliQty,0)," +

                    "',buycomMargin:',QUOTE(IFNULL(av.buycomMargin,0.00))," +
                    "',buycomCharge:',QUOTE(IFNULL(av.buycomCharge,0.00))," +
                    "',buycomQty:',IFNULL(av.buycomQty,0)," +

                    "',active:',IFNULL(av.active,0)," +
                  "'}')" +
                " json " +
                "FROM sklad.itemVendor sv " +
                "LEFT JOIN advert.epyVendors av USING (vendor_id) " +
                "WHERE sv.vendor_id=?"
            );
            ps.setString(1, vendid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json = rs.getString("json");
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyVendor()", e);
        }
	return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateEpyVendor()">

    public String updateEpyVendor(String vendid, String data) {

        // vals=    0        1      2       3     4     5     6         7     8         9    10     11     12    13     14    15   16   17     18       19     20       21    22     23     24      25       26    27     28   29   30  31
        // data=amazonMargin_0_amazonCharge_0_amazonQty_0_amzcompMargin_0_amzcompCharge_0_amzcompQty_0_ebayMargin_0_ebayCharge_0_ebayQty_0_ebayAlyMargin_0_ebayAlyCharge_0_ebayAlyQty_0_buycomMargin_0_buycomCharge_0_buycomQty_0_active_0
        String vals[] = data.split("_");

        try(Connection conn = dsw.getConnection()) {
            int id = 0;

            PreparedStatement ps = conn.prepareStatement("SELECT id FROM advert.epyVendors WHERE vendor_id=?");
	    ps.setString(1, vendid);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                id = rs.getInt("id");
            }
            rs.close();
            ps.close();

            if(id != 0) {
                ps = conn.prepareStatement(
                    "UPDATE LOW_PRIORITY advert.epyVendors SET active=?," +
                    "amazonMargin=?,amazonCharge=?,amazonQty=?," +
                    "amzcompMargin=?,amzcompCharge=?,amzcompQty=?," +
                    "ebayMargin=?,ebayCharge=?,ebayQty=?," +
                    "ebayAliMargin=?,ebayAliCharge=?,ebayAliQty=?," +
                    "buycomMargin=?,buycomCharge=?,buycomQty=? " +
                    "WHERE id=?");
                ps.setString(1, vals[31]);  // active

                ps.setString(2, vals[1]);   // amazonMargin
                ps.setString(3, vals[3]);   // amazonCharge
                ps.setString(4, vals[5]);   // amazonQty

                ps.setString(5, vals[7]);   // amzcompMargin
                ps.setString(6, vals[9]);   // amzcompCharge
                ps.setString(7, vals[11]);  // amzcompQty

                ps.setString(8, vals[13]);  // ebayMargin
                ps.setString(9, vals[15]);  // ebayCharge
                ps.setString(10, vals[17]); // ebayQty

                ps.setString(11, vals[19]);  // ebayAliMargin
                ps.setString(12, vals[21]);  // ebayAliCharge
                ps.setString(13, vals[23]);  // ebayAliQty

                ps.setString(14, vals[25]);  // buycomMargin
                ps.setString(15, vals[27]);  // buycomCharge
                ps.setString(16, vals[29]);  // buycomQty

                ps.setInt(17, id);
                ps.executeUpdate();
                ps.close();
            }
            else {
                ps = conn.prepareStatement(
                    "INSERT LOW_PRIORITY advert.epyVendors SET vendor_id=?,active=?," +
                    "amazonMargin=?,amazonCharge=?,amazonQty=?," +
                    "amzcompMargin=?,amzcompCharge=?,amzcompQty=?," +
                    "ebayMargin=?,ebayCharge=?,ebayQty=?," +
                    "ebayAliMargin=?,ebayAliCharge=?,ebayAliQty=?," +
                    "buycomMargin=?,buycomCharge=?,buycomQty=?");
                ps.setString(1, vendid);
                ps.setString(2, vals[31]);  // active

                ps.setString(3, vals[1]);   // amazonMargin
                ps.setString(4, vals[3]);   // amazonCharge
                ps.setString(5, vals[5]);   // amazonQty

                ps.setString(6, vals[7]);   // amzcompMargin
                ps.setString(7, vals[9]);   // amcompCharge
                ps.setString(8, vals[11]);  // amcompQty

                ps.setString(9, vals[13]);  // ebayMargin
                ps.setString(10, vals[15]); // ebayCharge
                ps.setString(11, vals[17]); // ebayQty

                ps.setString(12, vals[19]); // ebayAliMargin
                ps.setString(13, vals[21]); // ebayAliCharge
                ps.setString(14, vals[23]); // ebayAliQty

                ps.setString(15, vals[25]); // buycomMargin
                ps.setString(16, vals[27]); // buycomCharge
                ps.setString(17, vals[29]); // buycomQty
                ps.executeUpdate();
                ps.close();
            }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "updateEpyVendor()", e);
        }
	return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyVendorCategories()">

    public String getEpyVendorCategories(String vendid) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            // get groups
            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{cid:',si.category_id," +
                "',name:',QUOTE(sc.name)," +
                "',amazonSend:',IFNULL(avs.amazonSend,0)," +
                "',amzcompSend:',IFNULL(avs.amzcompSend,0)," +
                "',ebaySend:',IFNULL(avs.ebaySend,0)," +
                "',ebayAliSend:',IFNULL(avs.ebayAliSend,0)," +
                "',buycomSend:',IFNULL(avs.buycomSend,0)," +
                "',cnt:',COUNT(*)," +
                "'}') json " +
                "FROM sklad.items si " +
                "LEFT JOIN sklad.category sc USING(category_id) " +
                "LEFT JOIN advert.epyVendorSubCategories avs ON (avs.vendor_id = si.vendor_id AND avs.category_id=si.category_id) " +
                "WHERE si.active='Y' AND si.vendor_id=? AND si.category_id<>0 " +
                "GROUP BY si.category_id " +
                "ORDER BY sc.name"
            );
            ps.setString(1, vendid);

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyVendorCategories()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveEpyVendorCategories()">

    public String saveEpyVendorCategories(String parms) {

        String[] checkbox = parms.split(":"),ids;

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement deleteBatch = conn.prepareStatement(
                "DELETE LOW_PRIORITY FROM advert.epyVendorSubCategories WHERE vendor_id=? AND category_id=?");
            PreparedStatement insertBatch = conn.prepareStatement(
                "INSERT LOW_PRIORITY advert.epyVendorSubCategories SET vendor_id=?,category_id=?,amazonSend=?,amzcompSend=?,ebaySend=?,ebayAliSend=?,buycomSend=?");

            //   ids=   0  1  2 3 4 5 6
            // parms = 89,132,1,0,1,0,1 -> where 89 - vendorid; 132 - subcatid; 0/1 - amazonSend; 0/1 - amcompSend; 0/1 - ebaySend; 0/1 - ebayAliSend; 0/1 - buycomSend;
            for(String cat: checkbox) {
                ids = cat.split(",");

                // delete all subcat
                deleteBatch.setString(1, ids[0]); // vendorid
                deleteBatch.setString(2, ids[1]); // subcategoryid
                deleteBatch.addBatch();

                // insert
                if("1".equals(ids[2]) || "1".equals(ids[3]) || "1".equals(ids[4]) || "1".equals(ids[5]) || "1".equals(ids[6])) {
                    insertBatch.setString(1, ids[0]); // vendorid
                    insertBatch.setString(2, ids[1]); // subcategoryid
                    insertBatch.setString(3, ids[2]); // amazonSend
                    insertBatch.setString(4, ids[3]); // amzcompSend
                    insertBatch.setString(5, ids[4]); // ebaySend
                    insertBatch.setString(6, ids[5]); // ebayAliSend
                    insertBatch.setString(7, ids[6]); // buycomSend
                    insertBatch.addBatch();
                }
            }
            deleteBatch.executeBatch();
            deleteBatch.close();

            insertBatch.executeBatch();
            insertBatch.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveEpyVendorCategories()", e);
        }
	return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyGroups()">

    public String getEpyGroups() {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            json.append("{cats:[");

            // get categories
            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(GROUP_CONCAT(" +
                  "CONCAT(" +
                    "'{id:',gc.category_id,'" +
                    ",name:',QUOTE(gc.name)," +
                  "'}')" +
                "),'') json " +
                "FROM sklad.category gc " +
                "LEFT JOIN advert.epyGroupSubCategory ec USING(category_id) " +
                "WHERE gc.parent<>0 AND gc.publish='Y' AND ec.category_id IS NULL " +
                "ORDER BY gc.name");

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            // get groups
            json.append("],groups:[");
            ps = conn.prepareStatement(
                "SELECT GROUP_CONCAT(" +
                  "CONCAT(" +
                    "'{id:',group_id," +
                    "',name:',QUOTE(name)," +

                    "',amazon:{'," +
                    "'defQty0_30:',amazon_defQty0_30," +
                    "',minQty0_30:',amazon_minQty0_30," +
                    "',margin0_30:',amazon_margin0_30," +
                    "',charge0_30:',amazon_charge0_30," +
                    "',defQty31_60:',amazon_defQty31_60," +
                    "',minQty31_60:',amazon_minQty31_60," +
                    "',margin31_60:',amazon_margin31_60," +
                    "',charge31_60:',amazon_charge31_60," +
                    "',defQty61_90:',amazon_defQty61_90," +
                    "',minQty61_90:',amazon_minQty61_90," +
                    "',margin61_90:',amazon_margin61_90," +
                    "',charge61_90:',amazon_charge61_90," +
                    "',defQty91_180:',amazon_defQty91_180," +
                    "',minQty91_180:',amazon_minQty91_180," +
                    "',margin91_180:',amazon_margin91_180," +
                    "',charge91_180:',amazon_charge91_180," +
                    "',defQty181_240:',amazon_defQty181_240," +
                    "',minQty181_240:',amazon_minQty181_240," +
                    "',margin181_240:',amazon_margin181_240," +
                    "',charge181_240:',amazon_charge181_240," +
                    "',defQty241_more:',amazon_defQty241_more," +
                    "',minQty241_more:',amazon_minQty241_more," +
                    "',margin241_more:',amazon_margin241_more," +
                    "',charge241_more:',amazon_charge241_more," +
                    "'}'," +

                    "',amzcomp:{'," +
                    "'defQty0_30:',amzcomp_defQty0_30," +
                    "',minQty0_30:',amzcomp_minQty0_30," +
                    "',margin0_30:',amzcomp_margin0_30," +
                    "',charge0_30:',amzcomp_charge0_30," +
                    "',defQty31_60:',amzcomp_defQty31_60," +
                    "',minQty31_60:',amzcomp_minQty31_60," +
                    "',margin31_60:',amzcomp_margin31_60," +
                    "',charge31_60:',amzcomp_charge31_60," +
                    "',defQty61_90:',amzcomp_defQty61_90," +
                    "',minQty61_90:',amzcomp_minQty61_90," +
                    "',margin61_90:',amzcomp_margin61_90," +
                    "',charge61_90:',amzcomp_charge61_90," +
                    "',defQty91_180:',amzcomp_defQty91_180," +
                    "',minQty91_180:',amzcomp_minQty91_180," +
                    "',margin91_180:',amzcomp_margin91_180," +
                    "',charge91_180:',amzcomp_charge91_180," +
                    "',defQty181_240:',amzcomp_defQty181_240," +
                    "',minQty181_240:',amzcomp_minQty181_240," +
                    "',margin181_240:',amzcomp_margin181_240," +
                    "',charge181_240:',amzcomp_charge181_240," +
                    "',defQty241_more:',amzcomp_defQty241_more," +
                    "',minQty241_more:',amzcomp_minQty241_more," +
                    "',margin241_more:',amzcomp_margin241_more," +
                    "',charge241_more:',amzcomp_charge241_more," +
                    "'}'," +

                    "',ebay:{'," +
                    "'defQty0_30:',ebay_defQty0_30," +
                    "',minQty0_30:',ebay_minQty0_30," +
                    "',margin0_30:',ebay_margin0_30," +
                    "',charge0_30:',ebay_charge0_30," +
                    "',defQty31_60:',ebay_defQty31_60," +
                    "',minQty31_60:',ebay_minQty31_60," +
                    "',margin31_60:',ebay_margin31_60," +
                    "',charge31_60:',ebay_charge31_60," +
                    "',defQty61_90:',ebay_defQty61_90," +
                    "',minQty61_90:',ebay_minQty61_90," +
                    "',margin61_90:',ebay_margin61_90," +
                    "',charge61_90:',ebay_charge61_90," +
                    "',defQty91_180:',ebay_defQty91_180," +
                    "',minQty91_180:',ebay_minQty91_180," +
                    "',margin91_180:',ebay_margin91_180," +
                    "',charge91_180:',ebay_charge91_180," +
                    "',defQty181_240:',ebay_defQty181_240," +
                    "',minQty181_240:',ebay_minQty181_240," +
                    "',margin181_240:',ebay_margin181_240," +
                    "',charge181_240:',ebay_charge181_240," +
                    "',defQty241_more:',ebay_defQty241_more," +
                    "',minQty241_more:',ebay_minQty241_more," +
                    "',margin241_more:',ebay_margin241_more," +
                    "',charge241_more:',ebay_charge241_more," +
                    "'}'," +

                    "',ebayali:{'," +
                    "'defQty0_30:',ebayali_defQty0_30," +
                    "',minQty0_30:',ebayali_minQty0_30," +
                    "',margin0_30:',ebayali_margin0_30," +
                    "',charge0_30:',ebayali_charge0_30," +
                    "',defQty31_60:',ebayali_defQty31_60," +
                    "',minQty31_60:',ebayali_minQty31_60," +
                    "',margin31_60:',ebayali_margin31_60," +
                    "',charge31_60:',ebayali_charge31_60," +
                    "',defQty61_90:',ebayali_defQty61_90," +
                    "',minQty61_90:',ebayali_minQty61_90," +
                    "',margin61_90:',ebayali_margin61_90," +
                    "',charge61_90:',ebayali_charge61_90," +
                    "',defQty91_180:',ebayali_defQty91_180," +
                    "',minQty91_180:',ebayali_minQty91_180," +
                    "',margin91_180:',ebayali_margin91_180," +
                    "',charge91_180:',ebayali_charge91_180," +
                    "',defQty181_240:',ebayali_defQty181_240," +
                    "',minQty181_240:',ebayali_minQty181_240," +
                    "',margin181_240:',ebayali_margin181_240," +
                    "',charge181_240:',ebayali_charge181_240," +
                    "',defQty241_more:',ebayali_defQty241_more," +
                    "',minQty241_more:',ebayali_minQty241_more," +
                    "',margin241_more:',ebayali_margin241_more," +
                    "',charge241_more:',ebayali_charge241_more," +
                    "'}'," +

                    "',buycom:{'," +
                    "'defQty0_30:',buycom_defQty0_30," +
                    "',minQty0_30:',buycom_minQty0_30," +
                    "',margin0_30:',buycom_margin0_30," +
                    "',charge0_30:',buycom_charge0_30," +
                    "',defQty31_60:',buycom_defQty31_60," +
                    "',minQty31_60:',buycom_minQty31_60," +
                    "',margin31_60:',buycom_margin31_60," +
                    "',charge31_60:',buycom_charge31_60," +
                    "',defQty61_90:',buycom_defQty61_90," +
                    "',minQty61_90:',buycom_minQty61_90," +
                    "',margin61_90:',buycom_margin61_90," +
                    "',charge61_90:',buycom_charge61_90," +
                    "',defQty91_180:',buycom_defQty91_180," +
                    "',minQty91_180:',buycom_minQty91_180," +
                    "',margin91_180:',buycom_margin91_180," +
                    "',charge91_180:',buycom_charge91_180," +
                    "',defQty181_240:',buycom_defQty181_240," +
                    "',minQty181_240:',buycom_minQty181_240," +
                    "',margin181_240:',buycom_margin181_240," +
                    "',charge181_240:',buycom_charge181_240," +
                    "',defQty241_more:',buycom_defQty241_more," +
                    "',minQty241_more:',buycom_minQty241_more," +
                    "',margin241_more:',buycom_margin241_more," +
                    "',charge241_more:',buycom_charge241_more," +
                    "'}'," +

                    "',act:',active," +
                  "'}')" +
                ") json " +
                "FROM advert.epyGroups");

            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append("]}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyGroups()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyGroup()">

    public String getEpyGroup(String groupid, String advname) {

	String adv = (advname != null && advname.length() != 0 ? advname + "_" : "");
	String json = "";
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{id:',group_id," +
                "',name:',QUOTE(name)," +
                "',defQty0_30:'," + adv + "defQty0_30," +
                "',minQty0_30:'," + adv + "minQty0_30," +
                "',margin0_30:'," + adv + "margin0_30," +
                "',charge0_30:'," + adv + "charge0_30," +

                "',defQty31_60:'," + adv + "defQty31_60," +
                "',minQty31_60:'," + adv + "minQty31_60," +
                "',margin31_60:'," + adv + "margin31_60," +
                "',charge31_60:'," + adv + "charge31_60," +

                "',defQty61_90:'," + adv + "defQty61_90," +
                "',minQty61_90:'," + adv + "minQty61_90," +
                "',margin61_90:'," + adv + "margin61_90," +
                "',charge61_90:'," + adv + "charge61_90," +

                "',defQty91_180:'," + adv + "defQty91_180," +
                "',minQty91_180:'," + adv + "minQty91_180," +
                "',margin91_180:'," + adv + "margin91_180," +
                "',charge91_180:'," + adv + "charge91_180," +

                "',defQty181_240:'," + adv + "defQty181_240," +
                "',minQty181_240:'," + adv + "minQty181_240," +
                "',margin181_240:'," + adv + "margin181_240," +
                "',charge181_240:'," + adv + "charge181_240," +

                "',defQty241_more:'," + adv + "defQty241_more," +
                "',minQty241_more:'," + adv + "minQty241_more," +
                "',margin241_more:'," + adv + "margin241_more," +
                "',charge241_more:'," + adv + "charge241_more," +

                "',active:',active," +
                "'}') json " +
                "FROM advert.epyGroups " +
                "WHERE group_id=?"
            );
            ps.setString(1, groupid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json = rs.getString("json");
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyGroup()", e);
        }
	return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateEpyGroup()">

    public String updateEpyGroup(String groupid, String parms) {

        StringBuilder sql = new StringBuilder("UPDATE LOW_PRIORITY advert.epyGroups SET ");
        String[] names = parms.split(",");
        for(int i = 0; i < names.length; i++) {
            sql.append((i > 0 ? "," : "")).append(names[i]);
        }
        sql.append(" WHERE group_id=").append(groupid);

        try(
            Connection conn = dsw.getConnection();
	    PreparedStatement us = conn.prepareStatement(sql.toString())) {

            us.executeUpdate();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "updateEpyGroup()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="createEpyGroup()">

    public String createEpyGroup(String parms) {

        StringBuilder sql = new StringBuilder("INSERT LOW_PRIORITY advert.epyGroups SET ");
        String[] names = parms.split(","),parm;
        for(int i = 0; i < names.length; i++) {
            sql.append((i > 0 ? "," : "")).append(names[i]);
        }

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement(sql.toString());
	    us.executeUpdate();
            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "createEpyGroup()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="eraseEpyGroup()">

    public String eraseEpyGroup(String groupid) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement("DELETE FROM advert.epyGroups WHERE group_id=?");
	    us.setString(1, groupid);
	    us.executeUpdate();
            us.close();

            us = conn.prepareStatement("DELETE FROM advert.epyGroupSubCategory WHERE group_id=?");
	    us.setString(1, groupid);
	    us.executeUpdate();
            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "eraseEpyGroup()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveEpyDrag()">

    public String saveEpyDrag(int newgroupid, int oldgroupid, String catid) {

        try(Connection conn = dsw.getConnection()) {

            PreparedStatement us;

            if(newgroupid == 0) {
                us = conn.prepareStatement("DELETE FROM advert.epyGroupSubCategory WHERE group_id=? AND category_id=?");
                us.setInt(1, oldgroupid);
                us.setString(2, catid);
                us.executeUpdate();
                us.close();
            }
            else if(oldgroupid == 0) {
                us = conn.prepareStatement("INSERT LOW_PRIORITY INTO advert.epyGroupSubCategory SET group_id=?,category_id=?");
                us.setInt(1, newgroupid);
                us.setString(2, catid);
                us.executeUpdate();
                us.close();
            }
            else {
                us = conn.prepareStatement("UPDATE LOW_PRIORITY advert.epyGroupSubCategory SET group_id=?,category_id=? WHERE  group_id=? AND category_id=?");
                us.setInt(1, newgroupid);
                us.setString(2, catid);
                us.setInt(3, oldgroupid);
                us.setString(4, catid);
                us.executeUpdate();
                us.close();
            }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveEpyDrag()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getSubCategory()">

    public String getEpySubCategory(String groupid) {

	String json = "";
        try(Connection conn = dsw.getConnection()) {

            // get groups
            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(GROUP_CONCAT(" +
                  "CONCAT(" +
                    "'{id:',IFNULL(gc.category_id,0)," +
                    "',name:',QUOTE(IFNULL(gc.name,''))," +
                  "'}')" +
                "),'') json " +
                "FROM advert.epyGroupSubCategory ec " +
                "LEFT JOIN sklad.category gc USING(category_id) " +
                "WHERE group_id=?"
            );
            ps.setString(1, groupid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json = rs.getString("json");
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getSubCategory()", e);
        }

        return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyItems()">

    public String getEpyItems(int currentPage, String catid) {

        int custOnPage = 40;
	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                "'{gname:',QUOTE(grp.name)," +
                "',iname:',QUOTE(itm.name)," +
                "',als:',QUOTE(itvnd.alias)," +
                "',vndname:',QUOTE(itvnd.name)," +
                "',defQty0_30:',grp.defQty0_30," +
                "',minQty0_30:',grp.minQty0_30," +
                "',margin0_30:',grp.margin0_30," +
                "',charge0_30:',grp.charge0_30," +
                "',defQty31_60:',grp.defQty31_60," +
                "',minQty31_60:',grp.minQty31_60," +
                "',margin31_60:',grp.margin31_60," +
                "',charge31_60:',grp.charge31_60," +
                "',defQty61_90:',grp.defQty61_90," +
                "',minQty61_90:',grp.minQty61_90," +
                "',margin61_90:',grp.margin61_90," +
                "',charge61_90:',grp.charge61_90," +
                "',defQty91_180:',grp.defQty91_180," +
                "',minQty91_180:',grp.minQty91_180," +
                "',margin91_180:',grp.margin91_180," +
                "',charge91_180:',grp.charge91_180," +
                "',defQty181_240:',grp.defQty181_240," +
                "',minQty181_240:',grp.minQty181_240," +
                "',margin181_240:',grp.margin181_240," +
                "',charge181_240:',grp.charge181_240," +
                "',defQty241_more:',grp.defQty241_more," +
                "',minQty241_more:',grp.minQty241_more," +
                "',margin241_more:',grp.margin241_more," +
                "',charge241_more:',grp.charge241_more," +
                "',sku:',QUOTE(itm.accwareCatID)," +
                "',sellPrice:',itm.sell_price," +
                "',hotPrice:',itm.hot_price," +
                "',stock:',IF(itm.stock='Y',1,0)," +
                "',qty:',itm.stock_quantity," +

                "',old0_30:',IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 0 AND 30,1)," +
                "',old31_60:',IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 31 AND 60,0)," +
                "',old61_90:',IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 61 AND 90,0)," +
                "',old91_180:',IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 91 AND 180,0)," +
                "',old181_240:',IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 181 AND 240,0)," +
                "',old241_more:',IFNULL(DATEDIFF(NOW(),itm.stockDate) >= 241,0)," +

                "',amazonMargin:',IFNULL(vend.amazonMargin,0)," +
                "',amazonCharge:',IFNULL(vend.amazonCharge,0)," +
                "',amazonQty:',IFNULL(vend.amazonQty,0)," +
                "',ebayMargin:',IFNULL(vend.ebayMargin,0)," +
                "',ebayCharge:',IFNULL(vend.ebayCharge,0)," +
                "',ebayQty:',IFNULL(vend.ebayQty,0)," +
                "',buycomMargin:',IFNULL(vend.buycomMargin,0)," +
                "',buycomCharge:',IFNULL(vend.buycomCharge,0)," +
                "',buycomQty:',IFNULL(vend.buycomQty,0)," +
                "',amazonSend:',IFNULL(vendcat.amazonSend,0)," +
                "',ebaySend:',IFNULL(vendcat.ebaySend,0)," +
                "',buycomSend:',IFNULL(vendcat.buycomSend,0)," +
                 "'}') json " +
                "FROM sklad.items itm " +
                "LEFT JOIN sklad.category cat USING(category_id) " +
                "LEFT JOIN sklad.itemVendor itvnd USING(vendor_id) " +
                "LEFT JOIN advert.epyGroupSubCategory grpcat USING(category_id) " +
                "LEFT JOIN advert.epyGroups grp USING(group_id) " +
                "LEFT JOIN advert.epyVendors vend USING(vendor_id) " +
                "LEFT JOIN advert.epyVendorSubCategories vendcat ON(vendcat.vendor_id=itm.vendor_id AND vendcat.category_id=itm.category_id) " +
                "WHERE itm.active='Y' AND itm.category_id=? LIMIT ?,?"
            );
            ps.setString(1, catid);
            ps.setInt(2, currentPage * custOnPage);
            ps.setInt(3, custOnPage);

            json.append("{rows:[");
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // item count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyItems()", e);
        }

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getEpyEditUser()">

    public String getEpyEditUser(String userid) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT GROUP_CONCAT(" +
                  "CONCAT(" +
                  "'{id:',user_id," +
                  "',name:',QUOTE(name)," +
                  "',act:',active," +
                  "'}')) json " +
                "FROM advert.epyUsers " +
                "WHERE user_id=?");
            ps.setString(1, userid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getEpyUser()", e);
        }

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateEpyUser()">

    public String updateEpyUser(String userid, String parms) {

        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement("UPDATE LOW_PRIORITY advert.epyUsers SET " + parms + " WHERE user_id=" + userid);
	    ps.executeUpdate();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "updateEpyUser()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="createEpyUser()">

    public String createEpyUser(String parms) {

        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement("INSERT LOW_PRIORITY advert.epyUsers SET " + parms);
	    ps.executeUpdate();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "createEpyUser()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="deleteEpyUser()">

    public String deleteEpyUser(String userid) {

        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement("DELETE FROM advert.epyUsers WHERE user_id=?");
            ps.setString(1, userid);
	    ps.executeUpdate();
            ps.close();

            ps = conn.prepareStatement("DELETE FROM advert.epyUserItems WHERE user_id=?");
            ps.setString(1, userid);
	    ps.executeUpdate();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "deleteEpyUser()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="attachItemToUser()">

    public String attachItemToUser(String itemid, String userid) {

        try(Connection conn = dsw.getConnection()) {
            int id = 0;
            int qty = 0;
            double price = 0d;

            PreparedStatement ps = conn.prepareStatement("SELECT stock_quantity qty,sell_price price FROM sklad.items WHERE item_id=?");
            ps.setString(1, itemid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                qty = rs.getInt("qty");
                price = rs.getDouble("price");
            }
            rs.close();
            ps.close();

            ps = conn.prepareStatement("SELECT id FROM advert.epyUserItems WHERE item_id=?");
            ps.setString(1, itemid);

            rs = ps.executeQuery();
            if(rs.next()) {
                id = rs.getInt("id");
            }
            rs.close();
            ps.close();

            if(id == 0) { // create new record
                ps = conn.prepareStatement(
                    "INSERT LOW_PRIORITY advert.epyUserItems SET item_id=?,user_id=?," +
                    "amazonQty=?,amazonPrice=?," +
                    "ebayQty=?,ebayPrice=?," +
                    "buycomQty=?,buycomPrice=?"
                );
                ps.setString(1, itemid);
                ps.setString(2, userid);
                ps.setInt(3, qty);
                ps.setDouble(4, price);
                ps.setInt(5, qty);
                ps.setDouble(6, price);
                ps.setInt(7, qty);
                ps.setDouble(8, price);
                ps.executeUpdate();
                ps.close();
            }
            else { // change attached item to other user
                ps = conn.prepareStatement(
                    "UPDATE LOW_PRIORITY advert.epyUserItems SET user_id=?," +
                    "amazonQty=?,amazonPrice=?," +
                    "ebayQty=?,ebayPrice=?," +
                    "buycomQty=?,buycomPrice=? " +
                    "WHERE id=?");
                ps.setString(1, userid);
                ps.setInt(2, qty);
                ps.setDouble(3, price);
                ps.setInt(4, qty);
                ps.setDouble(5, price);
                ps.setInt(6, qty);
                ps.setDouble(7, price);
                ps.setInt(8, id);
                ps.executeUpdate();
                ps.close();
            }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "attachItemToUser()", e);
        }

        return "";
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="COUPON">

// <editor-fold defaultstate="collapsed" desc="getCouponItem()">

    public String getCouponItem(String sku) {

	StringBuilder json = new StringBuilder();
        json.append("{");
        int itemid = 0;
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT it.item_id itemID," +
                "IFNULL(CONCAT(" +
                "'itm:{id:',it.item_id," +
                "',sku:',QUOTE(it.accwareCatID)," +
                "',iname:',QUOTE(it.name)," +
                "',cost:',it.cost_price," +
                "',sell:',it.sell_price," +
                "',hot:',it.hot_price," +
                "',drop:',it.drop_price," +
                "',stock:',QUOTE(it.stock)," +
                "',qty:',it.stock_quantity," +
                "',mfrName:',QUOTE(it.mfr_name)," +
                "',mfrNumber:',QUOTE(it.mfr_number)," +
                 "',cname:',QUOTE(gc.name)," +
                "',vname:',QUOTE(iv.name)," +
                "',older:',IFNULL(DATEDIFF(NOW(),it.stockDate),0)," +
                "'}'),'') json " +
                "FROM sklad.items it " +
                "LEFT JOIN sklad.category gc USING(category_id) " +
                "LEFT JOIN sklad.itemVendor iv USING(vendor_id) " +
                "WHERE it.active='Y' AND it.accwareCatID=?");
            ps.setString(1, sku);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                itemid = rs.getInt("itemID");
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            if(itemid != 0) {
                json.append(",cpns:[");
                ps = conn.prepareStatement(
                    "SELECT IFNULL(GROUP_CONCAT(CONCAT(" +
                    "'{id:',id," +
                    "',code:',QUOTE(code)," +
                    "',proc:',procent," +
                    "',price:',price," +
                    "',cnt:',count," +
                    "',used:',used," +
                    "',endDate:',QUOTE(endDate)," +
                    "',oldest:',DATEDIFF(endDate,NOW())," +
                    "'}')),'') json " +
                    "FROM sklad.itemsPromotionCode " +
                    "WHERE item_id=?");
                ps.setInt(1, itemid);

                rs = ps.executeQuery();
                if(rs.next()) {
                    json.append(rs.getString("json"));
                }
                rs.close();
                ps.close();
                json.append("]");
          }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getCouponItem()", e);
        }
        json.append("}");

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveCoupon()">

    public String saveCoupon(String iid, String code, String proc, String price, String date) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement(
                "INSERT LOW_PRIORITY sklad.itemsPromotionCode SET item_id=?,code=?,procent=?,price=?,endDate=?");
            us.setString(1, iid);
            us.setString(2, code);
            us.setString(3, proc);
            us.setString(4, price);
            us.setString(5, date);

            us.executeUpdate();
            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveCoupon()", e);
        }

        return "";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateCoupon()">

    public String updateCoupon(String id, String date) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement("UPDATE LOW_PRIORITY sklad.itemsPromotionCode SET endDate=? WHERE id=?");
            us.setString(1, date);
            us.setString(2, id);
	    us.executeUpdate();
            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "updateCoupon()", e);
        }

        return "";
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="ITEMTRACKING">

// <editor-fold defaultstate="collapsed" desc="getAdvert()">

    public String getAdvert() {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{aid:',ad.advert_id," +
                "',name:',QUOTE(ad.name)," +
                "',hits:',IFNULL(SUM(sd.hits),0)," +
                "',orders:',IFNULL(SUM(sd.orders),0)," +
                "',amount:',IFNULL(SUM(sd.amount),0)," +
                "',act:',IFNULL(ad.active,0)," +
                "'}'),'{}') json " +
                "FROM sklad.advert ad " +
                "LEFT JOIN sklad.statDomain sd ON (ad.advert_id=sd.advert_id AND sd.date=CURDATE()) " +
                "GROUP BY ad.advert_id " +
                "ORDER BY ad.active DESC");

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getAdvert()", e);
        }

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemTrackingCategory()">

    public String getItemTrackingCategory() {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{id:',gc.category_id," +
                "',name:',QUOTE(gc.name)," +
                "'}'),'{}') json " +
                "FROM sklad.category gc " +
                "WHERE gc.parent=0 AND publish='Y' " +
                "ORDER BY gc.name");

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getItemTrackingCategory()", e);
        }

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemTrackingSubcategory()">

    public String getItemTrackingSubcategory(String advertid, String subcatid) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            json.append("{sub:[");
            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{cid:',sc.category_id," +
                "',cname:',QUOTE(sc.name)," +
                "',vid:',it.vendor_id," +
                "',vname:',QUOTE(iv.name)," +
                "',alias:',QUOTE(iv.alias)," +
                "',cnt:',count(*)," +
                "'}'),'{}') json " +
                "FROM sklad.items it " +
                "LEFT JOIN sklad.category sc USING(category_id) " +
                "LEFT JOIN sklad.itemVendor iv USING(vendor_id) " +
                "WHERE it.active='Y' AND sc.publish='Y' AND sc.parent=? " +
                "GROUP BY it.vendor_id,sc.category_id");
            ps.setString(1, subcatid);

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            idx = 0;
            json.append("],grp:[");
            ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{gid:',ads.advert_id," +
                "',gname:',QUOTE(aad.name)," +
                "',vid:',ads.vendor_id," +
                "',cid:',ads.subcat_id," +
                "'}'),'{}') json " +
                "FROM advert.advertSend ads " +
                "LEFT JOIN sklad.advert aad USING(advert_id) " +
                "WHERE subcat_id IN(" +
                  "SELECT category_id FROM sklad.category WHERE parent=? AND publish='Y'" +
                ") AND ads.advert_id=?"
             );
            ps.setString(1, subcatid);
            ps.setString(2, advertid);

            rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append("]}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getItemTrackingSubcategory()", e);
        }

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveCategories()">

    public String saveItemTrackingSubcategories(String advertid, String catid, String cats) {

        String[] checkbox = cats.split(",");
        try(Connection conn = dsw.getConnection()) {
            String[] ids;
            String subcatid, vendid;
            boolean chbox;

	    PreparedStatement deleteBatch = conn.prepareStatement(
                "DELETE LOW_PRIORITY FROM advert.advertSend " +
                "WHERE advert_id=? AND vendor_id=? AND subcat_id IN(" +
                "  SELECT category_id FROM sklad.category WHERE parent=?" +
                ")");

            PreparedStatement insertBatch = conn.prepareStatement(
                "INSERT LOW_PRIORITY advert.advertSend SET advert_id=?,subcat_id=?,vendor_id=?");

            //   ids=  0  1 3
            // cats = 651_2_0 -> where 651 - subcatid; 2 - vendorid; 0/1 - checkbox
            for(String cat : checkbox) {
                ids = cat.split("_");
                subcatid = ids[0];
                vendid = ids[1];
                chbox = "1".equals(ids[2]) ? true: false;

                deleteBatch.setString(1, advertid);
                deleteBatch.setString(2, vendid);
                deleteBatch.setString(3, catid);
                deleteBatch.addBatch();

                // insert
                if(chbox) {
                    insertBatch.setString(1, advertid);
                    insertBatch.setString(2, subcatid);
                    insertBatch.setString(3, vendid);
                    insertBatch.addBatch();
                }
            }
            deleteBatch.executeBatch();
            deleteBatch.close();

            insertBatch.executeBatch();
            insertBatch.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveItemTrackingSubcategories()", e);
        }
	return "";
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="FroogleFeed">

// <editor-fold defaultstate="collapsed" desc="getGooleFeedMfrNumber()">

    public String getGooleFeedMfrNumber(String mfr) {

	String json = "";
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(GROUP_CONCAT(CONCAT(" +
                "'{iid:',it.item_id," +
                "',name:',QUOTE(it.name)," +
                "',price:',it.sell_price," +
                "'}')),'{}') json " +
                "FROM sklad.items it " +
                "WHERE active='Y' AND mfr_number LIKE '" + mfr + "%'");

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json = rs.getString("json");
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getGooleFeedMfrNumber()", e);
        }
        return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsForGoogle()">

    public String getItemsForGoogle(int currentPage, int vendorid, int cid, int userid, int appleid, int sortid, String orderby) {

        String vendorID = " ";
        if(vendorid != 0) {
            vendorID = " AND itm.vendor_id=" + vendorid + " ";
            if(userid != 0) {
                vendorID += " AND guh.user_id=" + userid + " ";
            }
        }

        String categoryID = " ";
        if(cid != 0) {
            categoryID = " AND itm.category_id=" + cid + " ";
        }
        // apple
        String filter = "";
        if(appleid != 0) {
            switch(appleid) {
                case 1: filter = "AND DATEDIFF(NOW(),itm.stockDate) BETWEEN 0 AND 30 "; break;
                case 2: filter = "AND DATEDIFF(NOW(),itm.stockDate) BETWEEN 31 AND 60 "; break;
                case 3: filter = "AND DATEDIFF(NOW(),itm.stockDate) BETWEEN 61 AND 90 "; break;
                case 4: filter = "AND DATEDIFF(NOW(),itm.stockDate) BETWEEN 91 AND 180 "; break;
                case 5: filter = "AND DATEDIFF(NOW(),itm.stockDate) BETWEEN 181 AND 240 "; break;
                case 6: filter = "AND DATEDIFF(NOW(),itm.stockDate) >= 241 "; break;
                case 7: filter = "AND itm.stockDate=0 "; break;
            }
        }
        // sort
        String fieldSort = "itm.accwareCatID ";
        if(sortid != 0) {
            switch(sortid) {
                case 1: fieldSort = "itm.accwareCatID "; break;     // by accwareCode
                case 2: fieldSort = "ad.mfrNumber "; break;         // by mfrNumber
                case 3: fieldSort = "itm.hot_price "; break;        // by reseller price
                case 4: fieldSort = "itm.sell_price "; break;       // by sell price
                case 5: fieldSort = "itm.stock_quantity "; break;   // by quantity
                case 6: fieldSort = "guh.date "; break;             // by Last Update
            }
        }

        int custOnPage = 40;
	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                "'{iid:',itm.item_id," +
                "',hist:',QUOTE(IFNULL(CONCAT(DATE_FORMAT(guh.date,'%m-%d'),' ',sus.name),''))," +
                "',name:',QUOTE(itm.name)," +
                "',sku:',QUOTE(itm.accwareCatID)," +
                "',costPrice:',itm.cost_price," +
                "',sellPrice:',itm.sell_price," +
                "',hotPrice:',itm.hot_price," +
                "',stock:',IF(itm.stock='Y',1,0)," +
                "',qty:',itm.stock_quantity," +
                "',mfrNumber:',QUOTE(ad.mfrNumber)," +
                "',drone:',itm.drone," +
                "',old:',IFNULL(DATEDIFF(NOW(),itm.stockDate),-1)," +
                "',reason:',QUOTE(ig.reason)," +
                "',moddate:',QUOTE(ig.moddate)," +
                "',expdate:',QUOTE(ig.expdate)," +
                "'}') json " +
                "FROM sklad.items itm " +
                "LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
                "LEFT JOIN sklad.itemsOnGoogle ig USING(item_id) " +
                "LEFT JOIN advert.googleUserHistory guh USING(history_id) " +
                "LEFT JOIN sklad.users sus USING(user_id) " +
                "LEFT JOIN sklad.category scat USING(category_id) " +
                "LEFT JOIN sklad.category gcat ON scat.parent=gcat.category_id " +
                "LEFT JOIN sklad.itemVendor itvnd USING(vendor_id) " +
                "WHERE itm.active='Y' AND gcat.category_id IS NOT NULL " +
                vendorID + categoryID + filter +
                "ORDER BY " + fieldSort + orderby + " LIMIT ?,?"
            );
            ps.setInt(1, currentPage * custOnPage);
            ps.setInt(2, custOnPage);

            json.append("{rows:[");
            ResultSet rs = ps.executeQuery();
            for(int idx = 0; rs.next();) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // item count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            // read categories
            if(cid == 0) {
                ps = conn.prepareStatement(
                    "SELECT CONCAT('{'," +
                    "'cid:',scat.parent," +
                    "',sid:',itm.category_id," +
                    "',cnt:',count(*)," +
                    "',scnm:',QUOTE(scat.name)," +
                    "',gcnm:',QUOTE(gcat.name)," +
                    "'}') jcat " +
                    "FROM sklad.items itm " +
                    "LEFT JOIN sklad.category scat USING(category_id) " +
                    "LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
                    "LEFT JOIN advert.googleUserHistory guh USING(history_id) " +
                    "LEFT JOIN sklad.category gcat ON scat.parent=gcat.category_id " +
                    "WHERE itm.active='Y' AND gcat.category_id IS NOT NULL " +
                    vendorID + filter +
                    "GROUP BY itm.category_id " +
                    "ORDER BY gcat.name,scat.name"
                );

                json.append(",cats:[");
                rs = ps.executeQuery();
                for(int idx = 0; rs.next();) {
                    json.append(idx++ > 0 ? "," : "").append(rs.getString("jcat"));
                }
                rs.close();
                ps.close();
                json.append("]");
            }

            json.append(",curPage:").append(currentPage);
            json.append(",onPage:").append(custOnPage);
            json.append("}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getItemsForGoogle()", e);
        }

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsSKUForGoogle()">

    public String getItemsSKUForGoogle(String accwareSKU) {

        int custOnPage = 40;
	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS CONCAT(" +
                "'{iid:',itm.item_id," +
                "',hist:',QUOTE(IFNULL(CONCAT(DATE_FORMAT(guh.date,'%Y-%m-%d'),' ',sus.name),''))," +
                "',name:',QUOTE(itm.name)," +
                "',sku:',QUOTE(itm.accwareCatID)," +
                "',costPrice:',itm.cost_price," +
                "',sellPrice:',itm.sell_price," +
                "',hotPrice:',itm.hot_price," +
                "',stock:',IF(itm.stock='Y',1,0)," +
                "',qty:',itm.stock_quantity," +
                "',mfrNumber:',QUOTE(ad.mfrNumber)," +
                "',drone:',itm.drone," +
                "',old:',IFNULL(DATEDIFF(NOW(),itm.stockDate),-1)," +
                "',reason:',QUOTE(ig.reason)," +
                "',moddate:',QUOTE(ig.moddate)," +
                "',expdate:',QUOTE(ig.expdate)," +
                 "'}') json " +
                "FROM sklad.items itm " +
                "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
                "LEFT JOIN sklad.itemsOnGoogle ig USING(item_id) " +
                "LEFT JOIN advert.googleUserHistory guh USING(history_id) " +
                "LEFT JOIN sklad.users sus USING(user_id) " +
                "LEFT JOIN sklad.category scat USING(category_id) " +
                "LEFT JOIN sklad.category gcat ON scat.parent=gcat.category_id " +
                "LEFT JOIN sklad.itemVendor itvnd USING(vendor_id) " +
                "WHERE itm.active='Y' AND gcat.category_id IS NOT NULL " +
                "AND itm.accwareCatID=?"
            );
            ps.setString(1, accwareSKU);

            int idx = 0;
            json.append("{rows:[");
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]");

            // item count
            ps = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(",cntPage:").append(rs.getInt("cnt"));
            }
            rs.close();
            ps.close();

            json.append(",curPage:0");
            json.append(",onPage:").append(custOnPage);
            json.append("}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getItemsSKUForGoogle()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVendorsForGoole()">

    public String getVendorsForGoole() {

	StringBuilder json = new StringBuilder("{vends:[");
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT GROUP_CONCAT(CONCAT(" +
                "'{vid:',vendor_id," +
                "',als:',QUOTE(alias)," +
                "',name:',QUOTE(name)," +
                "'}')) json " +
                "FROM sklad.itemVendor");

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append("],users:[");

            ps = conn.prepareStatement(
                "SELECT GROUP_CONCAT(CONCAT(" +
                "'{uid:',user_id," +
                "',name:', QUOTE(name)," +
                "'}')) json " +
                "FROM sklad.users " +
                "WHERE user_id!=1 " +
                "ORDER BY name");

            rs = ps.executeQuery();
            while(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getVendorsForGoole()", e);
        }
        json.append("]}");

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getUserHistory()">

    public String getUserHistory(String iid) {

	StringBuilder json = new StringBuilder();
        json.append('[');
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{iid:',agh.item_id," +
                "',name:',QUOTE(su.name)," +
                "',oldsell:',agh.oldSellPrice," +
                "',newsell:',agh.newSellPrice," +
                "',oldresl:',agh.oldResellerPrice," +
                "',newresl:',agh.newResellerPrice," +
                "',date:',QUOTE(agh.date)," +
                "'}') json " +
                "FROM advert.googleUserHistory agh " +
                "LEFT JOIN sklad.users su USING(user_id) " +
                "WHERE agh.item_id=? " +
                "ORDER BY agh.date DESC " +
                "LIMIT 10");
            ps.setString(1, iid);

            ResultSet rs = ps.executeQuery();
            for(int idx = 0; rs.next();) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getUserHistory()", e);
        }
        json.append(']');

        return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getDiscount()">

    public String getDiscount(int catid) {

	String json = "";
        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{cid:',category_id," +
                "',sid:',parent," +
                "',dct:',discount," +
                "',appl0:',apple0," +
                "',appl1:',apple1," +
                "',appl2:',apple2," +
                "',appl3:',apple3," +
                "',appl4:',apple4," +
                "',appl5:',apple5," +
                "'}') json " +
                "FROM sklad.category " +
                "WHERE category_id=? ");
            ps.setInt(1, catid);

            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json = rs.getString("json");
            }
            rs.close();
            ps.close();

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getDiscount()", e);
        }

        return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveDiscount()">

    public void saveDiscount(int catid, int scatid, double discount, double apple0, double apple1, double apple2, double apple3, double apple4, double apple5) {

        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement("UPDATE sklad.category SET discount=?,apple0=?,apple1=?,apple2=?,apple3=?,apple4=?,apple5=? WHERE category_id=?");
            ps.setDouble(1, discount);
            ps.setDouble(2, apple0);
            ps.setDouble(3, apple1);
            ps.setDouble(4, apple2);
            ps.setDouble(5, apple3);
            ps.setDouble(6, apple4);
            ps.setDouble(7, apple5);
            ps.setInt(8, catid);
            ps.executeUpdate();
            ps.close();

            // if this category change all discount in subcategory
            if(scatid == 0) {
                ps = conn.prepareStatement("UPDATE sklad.category SET discount=?,apple0=?,apple1=?,apple2=?,apple3=?,apple4=?,apple5=? WHERE parent=?");
                ps.setDouble(1, discount);
                ps.setDouble(2, apple0);
                ps.setDouble(3, apple1);
                ps.setDouble(4, apple2);
                ps.setDouble(5, apple3);
                ps.setDouble(6, apple4);
                ps.setDouble(7, apple5);
                ps.setInt(8, catid);
                ps.executeUpdate();
                ps.close();
            }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "saveDiscount()", e);
        }

    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="DropPrice & PromoCode">

// <editor-fold defaultstate="collapsed" desc="getPromoPriceItem()">

    public String getPromoPriceItem(String itemid, String email) {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            json.append("{");

            PreparedStatement ps, ps1;
            ResultSet rs, rs1;

            ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'item:{'," +
                    "'id:',i.item_id," +
                    "',sku:',QUOTE(i.accwareCatID)," +
                    "',name:',QUOTE(i.name)," +
                    "',costPrice:',i.cost_price," +
                    "',sellPrice:',i.sell_price," +
                    "',resPrice:',i.hot_price," +
                    "',dropPrice:',i.drop_price," +
                    "',qty:',i.stock_quantity," +
                    "',mfrName:',QUOTE(i.mfr_name)," +
                    "',mfrNumber:',QUOTE(i.mfr_number)," +
                    "',vendor:',QUOTE(v.name)," +
                    "',stockDate:',QUOTE(IFNULL(DATEDIFF(NOW(),i.stockDate),0))," +
                "'}') json " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.itemVendor v USING(vendor_id) " +
                "WHERE i.active='Y' AND i.item_id=? LIMIT 1"
            );
            ps.setString(1, itemid);

            rs = ps.executeQuery();
            if(rs.next()) {
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            int customerid = 0;
            ps = conn.prepareStatement(
                "SELECT customer_id," +
                "CONCAT(" +
                "',cust:{'," +
                    "'shipp:{'," +
                        "'company:',QUOTE(shippingCompany)," +
                        "',name:',QUOTE(CONCAT(shippingFirstName,' ',shippingLastName))," +
                        "',address:',QUOTE(CONCAT(shipping_address1,' ',shipping_address2))," +
                        "',city:',QUOTE(shipping_city)," +
                        "',state:',QUOTE(shipping_state)," +
                        "',country:',QUOTE(shipping_country)," +
                        "',zip:',QUOTE(shipping_zip)," +
                        "',phone:',QUOTE(shippingPhone)," +
                        "',cell:',QUOTE(shippingCell)," +
                        "',fax:',QUOTE(shippingFax)," +
                    "'},'," +
                    "'bill:{'," +
                        "'company:',QUOTE(billingCompany)," +
                        "',name:',QUOTE(CONCAT(billingFirstName,' ',billingLastName))," +
                        "',address:',QUOTE(CONCAT(billing_address1,' ',billing_address2))," +
                        "',city:',QUOTE(billing_city)," +
                        "',state:',QUOTE(billing_state)," +
                        "',country:',QUOTE(billing_country)," +
                        "',zip:',QUOTE(billing_zip)," +
                        "',phone:',QUOTE(billingPhone)," +
                        "',cell:',QUOTE(billingCell)," +
                        "',fax:',QUOTE(billingFax)," +
                    "'}}') json " +
                "FROM sklad.customer " +
                "WHERE email=? LIMIT 1"
            );
            ps.setString(1, email);
            rs = ps.executeQuery();
            if(rs.next()) {
                customerid = rs.getInt("customer_id");
                json.append(rs.getString("json"));
            }
            rs.close();
            ps.close();

            json.append(",orders:[");
            ps = conn.prepareStatement(
                "SELECT order_id," +
                "CONCAT(" +
                "'{id:',order_id," +
                "',no:',QUOTE(orderNo)," +
                "',date:',QUOTE(order_date)," +
                "',cod:',QUOTE(cod)," +
                "',wrty:',QUOTE(warranty)," +
                "',shipPrice:',shipping_price," +
                "',shipCode:',QUOTE(shipping_code)," +
                "',tax:',tax_price," +
                "',amount:',amount," +
                "'') json " +
                "FROM sklad.orders " +
                "WHERE customer_id=? ORDER BY order_date DESC LIMIT 2"
            );
            ps.setInt(1, customerid);

            int idx = 0;
            rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));

                json.append(",itms:[");
                ps1 = conn.prepareStatement(
                    "SELECT " +
                    "CONCAT(" +
                    "'{id:',order_id," +
                    "',sku:',QUOTE(i.accwareCatID)," +
                    "',name:',QUOTE(i.name)," +
                    "',costPrice:',i.cost_price," +
                    "',sellPrice:',i.sell_price," +
                    "'}') json " +
                    "FROM sklad.order_line o " +
                    "LEFT JOIN sklad.items i USING(item_id) " +
                    "WHERE o.order_id=?"
                );
                ps1.setInt(1, rs.getInt("order_id"));

                int idx1 = 0;
                rs1 = ps1.executeQuery();
                while(rs1.next()) {
                    json.append(idx1++ > 0 ? "," : "").append(rs1.getString("json"));
                }
                rs1.close();
                ps1.close();
                json.append("]");
                json.append("}");

            }
            rs.close();
            ps.close();
            json.append("]");

            json.append("}");
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getPromoPriceItem()", e);
        }
	return json.toString();
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getStatus()">

    public String getStatus() {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{id:',id," +
                "',name:',QUOTE(name)," +
                "',url:',QUOTE(url)," +
                "',dir:',QUOTE(dir)," +
                "',cntAmazon:',cntAmazon," +
                "',cntAmzComp:',cntAmzComp," +
                "',cntEbay:',cntEbay," +
                "',cntEbayAli:',cntEbayAli," +
                "',cntBuycom:',cntBuycom," +
                "',time:',time," +
                "',bld:',QUOTE(build)," +
                "'}') json " +
                "FROM advert.stat"
            );
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getStatus()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="users">

// <editor-fold defaultstate="collapsed" desc="getUserTabsMask()">
/*
tabs Mask
fedc ba98 7654 3210
0000 0000 0000 0000 ----- not seccefull         FP - Start Page
|||| |||| |||| ||||______ 0x0001 (>> 0x0 & 0x1) WS - Web Status
|||| |||| |||| |||_______ 0x0020 (>> 0x1 & 0x1) CS - Customer
|||| |||| |||| ||________ 0x0040 (>> 0x2 & 0x1) RD - Orders
|||| |||| |||| |_________ 0x0080 (>> 0x3 & 0x1) RS - Resellers
|||| |||| ||||
|||| |||| ||||___________ 0x0010 (>> 0x4 & 0x1) GG - Google
|||| |||| |||____________ 0x0020 (>> 0x5 & 0x1) EP - Epy
|||| |||| ||_____________ 0x0040 (>> 0x6 & 0x1) CP - Coupon
|||| |||| |______________ 0x0080 (>> 0x7 & 0x1) IT - Item Track
|||| ||||
|||| ||||________________ 0x0100 (>> 0x8 & 0x1) GF - Google Feeds
|||| |||_________________ 0x0200 (>> 0x9 & 0x1) UP - Users profile
|||| ||__________________ 0x0400 (>> 0xA & 0x1)
|||| |___________________ 0x0800 (>> 0xB & 0x1)
||||
||||_____________________ 0x1000 (>> 0xC & 0x1)
|||______________________ 0x2000 (>> 0xD & 0x1)
||_______________________ 0x4000 (>> 0xE & 0x1)
|________________________ 0x8000 (>> 0xF & 0x1)
*/
    public String getUserTabsMask(String user, String passwd) {

	String json = "({name:'Guest', mask: 0, seltab:0})";

        if(user == null || passwd == null || user.length() == 0 || passwd.length() == 0) {
            return json;
        }

        try(Connection conn = dsw.getConnection()) {

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT('({uid:',user_id,',name:',QUOTE(name),',mask:',tab_mask,',seltab:',0,'})') json " +
                "FROM sklad.users " +
                "WHERE active=1 AND passwd<>'' AND name=? AND passwd=?"
            );
            ps.setString(1, user);
            ps.setString(2, passwd);
            
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                json = rs.getString("json");
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getUserTabsMask()", e);
        }

	return json;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getUsers()">

    public String getUsers() {

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT(" +
                "'{id:',user_id," +
                "',name:',QUOTE(name)," +
                "',passwd:',QUOTE(passwd)," +
                "',catid:',QUOTE(accwareCatID)," +
                "',mask:',tab_mask," +
                "',act:',active," +
                "'}') json " +
                "FROM sklad.users " +
                "WHERE user_id<>1"
            );

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getUsers()", e);
        }

	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateRootUser()">

    public String updateRootUser(String userid, String passwd, String mask, String active) {

        try(Connection conn = dsw.getConnection()) {

	    PreparedStatement us = conn.prepareStatement(
                "UPDATE LOW_PRIORITY sklad.users SET passwd=?,tab_mask=?,active=? WHERE user_id=?");
            us.setString(1, passwd);
            us.setString(2, mask);
            us.setString(3, active);
            us.setString(4, userid);

            us.executeUpdate();
            us.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "updateRootUser()", e);
        }
	return "";
    }
// </editor-fold>

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAmazonStatus()">

    public String getAmazonStatus() {

	StringBuilder json = new StringBuilder();

        try(Connection conn = dsw.getConnection()) {
            int idx = 0;

            PreparedStatement ps = conn.prepareStatement(
                "SELECT IFNULL(CONCAT(" +
                "'{id:',id," +
                "',pg1:',page1," +
                "',pg2:',page2," +
                "',pg3:',page3," +
                "'}'), '{id:0,pg1:0,pg2:0,pg3:0}') json " +
                "FROM webstat.amazon " +
                "WHERE date=CURRENT_DATE()"
            );

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getAmazonStatus()", e);
        }

	return json.length() == 0 ? "{id:0,pg1:0,pg2:0,pg3:0}" : json.toString();
    }
// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="getSuggesion()">

    public String getSuggesion(String query, String columName) {

        String colum = "accwareAccount";
        if(columName != null) {
            switch (columName) {
                case "accware":
                    colum = "accwareAccount"; break;
                case "email":
                    colum = "email"; break;
                case "no":
                    colum = "last_order_no"; break;
            }
        }

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;
            json.append("{query:\"").append(query).append("\",suggestions:[");

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT('{',customer_id,':\"'," + colum + ",'\"}') json " +
                "FROM sklad.customer " +
                "WHERE " + colum + " LIKE '" + query + "%'" +
                "LIMIT 10"
            );

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]}");

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getSuggesion()", e);
        }
	return json.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrderSuggesion()">

    public String getOrderSuggesion(String query, String columName) {

        String colum = "accwareAccount";
        if(columName != null) {
            if("accware".equals(columName)) {
                colum = "accwareAccount";
            }
            else if("no".equals(columName)) {
                colum = "orderNo";
            }
        }

	StringBuilder json = new StringBuilder();
        try(Connection conn = dsw.getConnection()) {
            int idx = 0;
            json.append("{query:\"").append(query).append("\",suggestions:[");

            PreparedStatement ps = conn.prepareStatement(
                "SELECT CONCAT('{',order_id,':\"'," + colum + ",'\"}') json " +
                "FROM sklad.orders " +
                "WHERE " + colum + " LIKE '" + query + "%'" +
                "LIMIT 10"
            );

            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                json.append(idx++ > 0 ? "," : "").append(rs.getString("json"));
            }
            rs.close();
            ps.close();
            json.append("]}");

        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getOrderSuggesion()", e);
        }
	return json.toString();
    }
// </editor-fold>

}
